package com.dao;

import com.bean.Student;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {
    //获取学生信息列表
    public static List<Student> getList() {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        List<Student> list = new ArrayList<>();
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            conn = BaseDao.getConnection();
            stmt = conn.prepareStatement("SELECT * FROM student");
            rs = stmt.executeQuery();
            while (rs.next()) {
                Student stu = new Student();
                stu.setId(rs.getString(1));
                stu.setName(rs.getString(3));
                stu.setSex(rs.getString(4));
                stu.setAge(rs.getInt(5));
                stu.setPhone(rs.getString(6));
                stu.setCno(rs.getString(2));
                list.add(stu);
            }
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            BaseDao.closeAll(rs, stmt, conn);
        }
        return list;
    }

    //增加学生信息
    public static void addStudent(Student student){
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = BaseDao.getConnection();
            stmt = conn.prepareStatement("INSERT INTO student (student_id,student_phone,student_name,student_sex,student_cno,student_age) VALUES (?,?,?,?,?,?);");
            stmt.setString(1, student.getId());
            stmt.setString(2, student.getPhone());
            stmt.setString(3, student.getName());
            stmt.setString(4, student.getSex());
            stmt.setString(5, student.getCno());
            stmt.setInt(6, student.getAge());
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.closeAll(rs, stmt, conn);
        }
    }

    //根据学号查找学生信息
    public static Student searchStudent(String id){
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rst = null;
        Student stu = new Student();
        try{
            conn = BaseDao.getConnection();
            pstmt = conn.prepareStatement("SELECT * FROM student WHERE student_id=?");
            pstmt.setString(1,id);
            rst = pstmt.executeQuery();
            if(rst.next()){
                stu.setId(rst.getString("student_id"));
                stu.setName(rst.getString("student_name"));
                stu.setPhone(rst.getString("student_phone"));
                stu.setCno(rst.getString("student_cno"));
                stu.setAge(rst.getInt("student_age"));
                stu.setSex(rst.getString("student_sex"));
                return stu;
            }else{
                return  null;
            }
        }catch(SQLException se){
            return null;
        }finally{
            try{
                conn.close();
            }catch(SQLException se){
            }
        }
    }




    //修改功能
    public static void updateStudent(Student student) {
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = BaseDao.getConnection();
            stmt = conn.prepareStatement("UPDATE student SET student_id=?,student_name=?,student_sex=?,student_cno=?,student_phone=?,student_age=? where student_id=?");
            stmt.setString(1, student.getId());
            stmt.setString(2, student.getName());
            stmt.setString(3, student.getSex());
            stmt.setString(4, student.getCno());
            stmt.setString(5, student.getPhone());
            stmt.setInt(6, student.getAge());
            stmt.setString(7, student.getId());
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.closeAll(rs, stmt, conn);
        }
    }

    //删除功能
    public static void deleteData(String id){
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = BaseDao.getConnection();
            stmt = conn.prepareStatement("DELETE FROM student WHERE student_id=?;");
            stmt.setString(1, id);
            stmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            BaseDao.closeAll(rs, stmt, conn);
        }
    }
}